﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Common;

namespace DAL
{
    public class EntityDAL : AbstractDAL<Entity>
    {
        static readonly string INSERT = "insert into entity values(null,@Title,@Content);select last_insert_rowid() newid;";
        static readonly string UPDATE = "update entity set Title=@Title,Content=@Content where id=@ID";
        static readonly string DELETE = "delete from entity where id=@ID";
        static readonly string SELECT = "select * from  entity where id=@ID";
        static readonly string SELECT_ALL = "select * from  entity";
        static readonly string SELECT_GetAllEntityCategoryKeyView = "select a.*,b.CID,d.Name as CategoryName,c.KID,e.Name as KeyName from entity a left join EntityCategory b on a.ID=b.EID left join EntityKey c on a.ID=c.EID left join Category d on b.cid=d.ID left join Key e on c.KID=e.ID";
        static readonly string SELECT_ALL_BY_CID = "select * from entity where id in(select distinct eid from entitycategory where cid in ({0}))";
        static readonly string SELECT_ALL_BY_KID = "select * from entity where id in(select distinct eid from entitykey where kid in ({0}))";
        static readonly string SELECT_ALL_BY_TITLE = "select * from  entity where Title like @Title";

        public List<Entity> GetAll()
        {
            List<Entity> list = new List<Entity>();
            DbDataReader reader = null;
            try
            {
                reader = SqlHelper.GetCommand(SELECT_ALL).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        public List<EntityCategoryKeyView> GetAllEntityCategoryKeyView()
        {
            List<EntityCategoryKeyView> list = new List<EntityCategoryKeyView>();
            DbDataReader reader = null;
            try
            {
                reader = SqlHelper.GetCommand(SELECT_GetAllEntityCategoryKeyView).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetEntityCategoryKeyViewModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        protected override string InsertCommand
        {
            get
            {
                return INSERT;
            }
        }

        protected override string UpdateCommand
        {
            get { return UPDATE; }
        }

        protected override string DeleteCommand
        {
            get { return DELETE; }
        }

        protected override string SelectCommand
        {
            get { return SELECT; }
        }

        protected override string SelectAllCommand
        {
            get { return SELECT_ALL; }
        }

        protected override DbParameter[] GetInsertParameter(Entity model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@Title", model.Title), SqlHelper.CreateParameter("@Content", model.Content) };
        }

        protected override void SyncID(Entity model, object scalar)
        {
            model.ID = (long)scalar;
        }

        protected override DbParameter[] GetUpdateParameter(Entity model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID), SqlHelper.CreateParameter("@Title", model.Title), SqlHelper.CreateParameter("@Content", model.Content) };
        }

        protected override DbParameter[] GetDeleteParameter(Entity model)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", model.ID) };
        }

        protected override DbParameter[] GetSeleteParameter(long id)
        {
            return new DbParameter[] { SqlHelper.CreateParameter("@ID", id) };
        }

        protected override Entity GetModel(DbDataReader reader)
        {
            Entity entity = new Entity();
            entity.ID = (long)reader["ID"];
            entity.Title = (string)reader["Title"];
            entity.Content = (string)reader["Content"];
            return entity;
        }

        protected EntityCategoryKeyView GetEntityCategoryKeyViewModel(DbDataReader reader)
        {
            EntityCategoryKeyView entity = new EntityCategoryKeyView { EntityItem = new Entity() };
            entity.EntityItem.ID = (long)reader["ID"];
            entity.EntityItem.Title = (string)reader["Title"];
            entity.EntityItem.Content = (string)reader["Content"];

            if (!reader.IsDBNull(3))
            {
                entity.CategoryItem = new Category();
                entity.CategoryItem.ID = (long)reader["CID"];
                entity.CategoryItem.Name = (string)reader["CategoryName"];
            }

            if (!reader.IsDBNull(5))
            {
                entity.KeyItem = new Key();
                entity.KeyItem.ID = (long)reader["KID"];
                entity.KeyItem.Name = (string)reader["KeyName"];
            }
            return entity;
        }

        public List<Entity> GetAllByCID(IList<long> cidList)
        {
            List<Entity> list = new List<Entity>();
            if (cidList.Count == 0) { return list; }

            DbDataReader reader = null;
            string sql = null;
            sql = string.Format(SELECT_ALL_BY_CID, string.Join(",", cidList));
            try
            {
                reader = SqlHelper.GetCommand(sql).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        public List<Entity> GetAllByKID(IList<long> kidList)
        {
            List<Entity> list = new List<Entity>();
            if (kidList.Count == 0) { return list; }

            DbDataReader reader = null;
            string sql = null;
            sql = string.Format(SELECT_ALL_BY_KID, string.Join(",", kidList));
            try
            {
                reader = SqlHelper.GetCommand(sql).ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        public List<Entity> GetAllByName(string keyword, List<long> cidList, List<long> kidList)
        {
            List<Entity> list = new List<Entity>();

            DbDataReader reader = null;
            DbCommand command = null;
            string sql = SELECT_ALL_BY_TITLE;

            if (cidList != null && cidList.Count != 0)
            {
                sql += string.Format(" and id in(select distinct eid from entitycategory where cid in ({0})) ", string.Join(",", cidList));
            }

            if (kidList != null && kidList.Count != 0)
            {
                sql += string.Format(" and id in(select distinct eid from entitykey where kid in ({0})) ", string.Join(",", kidList));
            }

            try
            {
                command = SqlHelper.GetCommand(sql);
                command.Parameters.Add(SqlHelper.CreateParameter("@Title", "%" + keyword + "%"));
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(GetModel(reader));
                }
            }
            finally
            {
                SqlHelper.Close(command);
            }
            return list;
        }
    }
}
